package com.atguigu5;

import com.beans.Customers;
import com.utils.JDBCUtils;
import org.junit.Test;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.*;

/**
 * 
 * @auther user
 * @date 2021/12/8 - 17:34
 */
public class BlobTest {
    //向customers表中插入一条Blob类型字段
    @Test
    public void testInsertCustomers() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            //1 获取连接
            connection = JDBCUtils.getConnection();
            //2 预编译sql，返回preparedStatment实例
            String slq = "insert into customers(name,email,birth,photo) values(?,?,?,?)";
            preparedStatement = connection.prepareStatement(slq);
            //3 填充占位符
            preparedStatement.setObject(1,"章鱼");
            preparedStatement.setObject(2,"zhangyu@qq.com");
            preparedStatement.setObject(3,"1998-09-17");
            FileInputStream fileInputStream = new FileInputStream(new File("test1.jpg"));
            preparedStatement.setBlob(4,fileInputStream);
            //4 执行
            int executeUpdate = preparedStatement.executeUpdate();
            if (executeUpdate == 1) {
                System.out.println("添加成功");
            }else {
                System.out.println("添加失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            //5 关闭资源
            JDBCUtils.closeResource(connection,preparedStatement);
        }
    }
    // 查询Customers表中的
    @Test
    public void testQueryCustomers() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //1 获取连接
            connection = JDBCUtils.getConnection();
            //2 预编译sql,返回PrepartedStatment实例
            String sql = "select id,name,email,birth,photo from customers where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            //3 填充占位符
            preparedStatement.setObject(1,19);
            //4 执行，返回ResultSet结果集
            resultSet = preparedStatement.executeQuery();
            //5 处理结果集
            if (resultSet.next()){
                //方式一
    //            int id = resultSet.getInt(1);
    //            String name = resultSet.getString(2);
    //            String email = resultSet.getString(3);
    //            Date birth = resultSet.getDate(4);
                //方式二
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                Date birth = resultSet.getDate("birth");
                Customers customers = new Customers(id,name,email,birth);
                System.out.println("customers = " + customers);

                //将Blob类型字段，下载下来，保存在本地
                Blob photo = resultSet.getBlob("photo");
                InputStream inputStream = photo.getBinaryStream();
                FileOutputStream fileOutputStream = new FileOutputStream("zhangyuhao.jpg");
                byte[] buffer = new byte[1024];
                int len;
                while ((len = inputStream.read(buffer)) != -1){
                    fileOutputStream.write(buffer,0,len);
                }
                //关闭资源
                inputStream.close();
                fileOutputStream.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        //关闭资源

        JDBCUtils.closeResource(connection,preparedStatement,resultSet);
    }
}
